package web.util;

import java.util.HashMap;
import java.util.Map;

// 尚未支持日期类型
public class DaoUtil {
	public static final String SELECT = "SELECT ";
	public static final String FROM = " FROM ";
	public static final String AND = " AND ";
	public static final String WHERE = " WHERE ";
	public static final String LIMIT = " LIMIT ";
	public static final String ALL_SYMBOL = "*";
	public static final String FUZZY_SYMBOL = "%";
	public static final String FUN_COUNT = "COUNT(*)"; // 此还需要仔细考虑，未必都是*
	
	public static enum SqlOperator {
		equals(" = "), 
		more(" > "), 
		less(" < "), 
		moreEquals(" >= "), 
		lessEquals(" <= "), 
		like(" LIKE "), 
		isNull(" IS NULL "), 
		isNotNull(" IS NOT NULL ");
		private String operator;

		private SqlOperator(String operator) {
			this.operator = operator;
		}

		public String getOperator() {
			return operator;
		}
	}
	
    /* 分页-模糊查询语句组合 Object[]  第一位存放=或者like > <等符号*/
    public static String getFuzzyQueryPageSql(String tableName, Map<String, Object[]> filter, int pageNum, int pageSize) {
        StringBuffer sql = new StringBuffer(SELECT + ALL_SYMBOL + FROM + tableName);
        arristSql(sql, filter);
        sql.append(LIMIT + (pageNum - 1) * pageSize + ", " + pageSize);
        return sql.toString();
    }
    /* 总纪录数-模糊查询语句组合Object[]   第一位存放=或者like > <等符号*/
    public static String getFuzzyQueryCountSql(String tableName, Map<String, Object[]> filter) {
        StringBuffer sql = new StringBuffer(SELECT + FUN_COUNT + FROM + tableName);
        arristSql(sql, filter);
        return sql.toString();
    }

    public static void arristSql(StringBuffer sql, Map<String, Object[]> filter) {
        if (null != filter && !filter.isEmpty()) {
            sql .append(WHERE);
            int count = 0;
            for (Map.Entry<String, Object[]> entry : filter.entrySet()) {
            	Object[] val = entry.getValue();
            	if (val == null) 
            		continue;
            	String sketch = arristSqlFilter(entry.getKey(), val);
            	if (sketch != null) {
            		sql.append(sketch);
            		if (count != filter.size() - 1) {
            			sql.append(AND);
            		}
            	}
                count++;
            }
        }
    }
    private static String getColValue(Object val) {
    	String result = null;
    	if (val instanceof Integer) {
    		result = String.valueOf(val);
    	} else {
    		result = "'" + String.valueOf(val) + "'";
    	}
    	return result;
    }
    
    /* 插入like使用的%模糊匹配 */
    private static String insertFuzzySymbol(String val, int type) {
    	if (!val.contains("'")) {
    		if (type == 1) {// 左侧插入%
    			return " %" + val.trim() + " ";
    		} else if (type == 2) {// 右侧插入%
    			return " " + val.trim() + "% ";
    		} else {// 两侧插入%  -1
    			return " %" + val.trim() + "% ";
    		}
    	} else {
    		if (type == 1) {// 左侧插入%
    			return "'%" + val.substring(1, val.length());
    		} else if (type == 2) {// 右侧插入%
    			return val.substring(0, val.length() - 1) + "%'";
    		} else {// 两侧插入%  -1
    			return "'%" + val.substring(1, val.length() - 1) + "%'";
    		}
    	}
    }
    
    public static String arristSqlFilter(String colName, Object[] val) {
    	StringBuffer result = new StringBuffer(colName);
    	if (val[0] == null)
    		return null;
    	SqlOperator op = (SqlOperator)val[0];
    	result.append(op.operator);
    	switch (op) {
		case equals:
			result.append(getColValue(val[1]));
			break;
		case more:
			result.append(getColValue(val[1]));
			break;
		case less:
			result.append(getColValue(val[1]));
			break;
		case moreEquals:
			result.append(getColValue(val[1]));
			break;
		case lessEquals:
			result.append(getColValue(val[1]));
			break;
		case like:
			result.append(insertFuzzySymbol(getColValue(val[1]), -1));
			break;
		case isNull:
			break;
		case isNotNull:
			break;
		default:
			throw new RuntimeException("暂不支持此种SQL操作类型！");
		}
    	return result.toString();
    }
    
    public static void main(String[] args) {
    	String tableName = "STUDENT";
    	Map<String, Object[]> filter = new HashMap<String, Object[]>();
    	filter.put("NAME", new Object[]{ SqlOperator.equals, "张三" });
    	filter.put("AGE", new Object[]{ SqlOperator.more, 30 });
    	filter.put("ADDRESS", new Object[]{ SqlOperator.like, "克拉玛依" });
    	filter.put("STUDENTNUMBER", new Object[]{ SqlOperator.isNull, null });
    	int pageNum = 2;
    	int pageSize = 10;
    	System.out.println(DaoUtil.getFuzzyQueryCountSql(tableName, filter));
    	System.out.println(DaoUtil.getFuzzyQueryPageSql(tableName, filter, pageNum, pageSize));
	}
}
